
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/****** Object:  Table [dbo].[Persons]    Script Date: 07/02/2011 20:29:31 ******/

CREATE TABLE [dbo].[Persons](
	[PersonID]   [int]          IDENTITY(1,1) NOT NULL,
	[Title]      [nvarchar](20)                    NULL,
	[FirstName]  [nvarchar](50)               NOT NULL,
	[LastName]   [nvarchar](50)               NOT NULL,
	[EntryDate]  [datetime]                   NOT NULL,
	[CreateDate] [datetime]                   NOT NULL,
	[ModifyDate] [datetime]                       NULL,
 CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED 
(
	[PersonID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Persons] ADD CONSTRAINT [DF_Persons_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO

CREATE NONCLUSTERED INDEX [FILTERED_FirstName] ON [dbo].[Persons] 
(
	[FirstName] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [FILTERED_LastName] ON [dbo].[Persons] 
(
	[LastName] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO



/****** Object:  Table [dbo].[Address]    Script Date: 07/02/2011 20:30:04 ******/

CREATE TABLE [dbo].[Address](
	[AddressID]    [int] IDENTITY(1,1) NOT NULL,
	[PersonID]     [int] UNIQUE        NOT NULL,
	[AddressLine1] [nvarchar](40)      NOT NULL,
	[AddressLine2] [nvarchar](30)          NULL,
	[AddressLine3] [nvarchar](30)          NULL,
	[AddressLine4] [nvarchar](30)          NULL,
	[PostalCode]   [nvarchar](30)          NULL,
	[Country]      [nvarchar](50)          NULL,
	[PhoneNumber]  [nvarchar](50)          NULL,
	[Email]        [nvarchar] (100)        NULL,
	[CreateDate]   [datetime]          NOT NULL,
	[ModifyDate]   [datetime]              NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
	[AddressID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [FILTERED_AddressLine1] ON [dbo].[Address] 
(
	[AddressLine1] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [FILTERED_Email] ON [dbo].[Address] 
(
	[Email] ASC
)WHERE [Email] is not null
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Address]  WITH CHECK ADD CONSTRAINT [FK_Address_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO

ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_Persons]
GO

ALTER TABLE [dbo].[Address] ADD  CONSTRAINT [DF_Address_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO



/****** Object:  Table [dbo].[PersonsExpired]    Script Date: 07/02/2011 20:29:31 ******/

CREATE TABLE [dbo].[PersonsExpired](
	[PersonExpiredID] [int]      IDENTITY(1,1) NOT NULL,
	[PersonID]        [int]      UNIQUE        NOT NULL,
	[Enable]          [bit]                    NOT NULL,
	[Expired]         [datetime]                   NULL,
	[CreateDate]      [datetime]               NOT NULL,
	[ModifyDate]      [datetime]                   NULL,
 CONSTRAINT [PK_PersonsExpired] PRIMARY KEY CLUSTERED 
(
	[PersonExpiredID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PersonsExpired]  WITH CHECK ADD CONSTRAINT [FK_PersonsExpired_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO

ALTER TABLE [dbo].[PersonsExpired] CHECK CONSTRAINT [FK_PersonsExpired_Persons]
GO

ALTER TABLE [dbo].[PersonsExpired] ADD CONSTRAINT [DF_PersonsExpired_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO



/****** Object:  Table [dbo].[TransactionModes]    Script Date: 07/02/2011 20:34:53 ******/

CREATE TABLE [dbo].[TransactionModes](
	[TransactionModeID]   [int]          IDENTITY(1,1) NOT NULL,
	[TransactionModeName] [nvarchar](20) UNIQUE        NOT NULL,
	[CreateDate]          [datetime]                   NOT NULL,
	[ModifyDate]          [datetime]                       NULL,
 CONSTRAINT [PK_TransactionMode] PRIMARY KEY CLUSTERED 
(
	[TransactionModeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TransactionModes] ADD CONSTRAINT [DF_TransactionModes_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO

INSERT INTO [dbo].[TransactionModes] ([TransactionModeName]) VALUES ('Cash')
INSERT INTO [dbo].[TransactionModes] ([TransactionModeName]) VALUES ('Cheque')
INSERT INTO [dbo].[TransactionModes] ([TransactionModeName]) VALUES ('DD')
INSERT INTO [dbo].[TransactionModes] ([TransactionModeName]) VALUES ('MO')
INSERT INTO [dbo].[TransactionModes] ([TransactionModeName]) VALUES ('BankTransaction')
GO



/****** Object:  Table [dbo].[PostalModes]    Script Date: 07/02/2011 20:35:28 ******/

CREATE TABLE [dbo].[PostalModes](
	[PostalModeID]   [int]          IDENTITY(1,1) NOT NULL,
	[PostalModeName] [nvarchar](20) UNIQUE NOT NULL,
 CONSTRAINT [PK_PostalMode] PRIMARY KEY CLUSTERED 
(
	[PostalModeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[PostalModes] ([PostalModeName]) VALUES ('Post')
INSERT INTO [dbo].[PostalModes] ([PostalModeName]) VALUES ('Courier')
INSERT INTO [dbo].[PostalModes] ([PostalModeName]) VALUES ('Hand')
INSERT INTO [dbo].[PostalModes] ([PostalModeName]) VALUES ('RegisteredPost')
GO



/****** Object:  Table [dbo].[DonationPlace]    Script Date: 07/02/2011 20:34:16 ******/

CREATE TABLE [dbo].[DonationPlace](
	[DonationPlaceID]   [int]          IDENTITY(1,1) NOT NULL,
	[DonationPlaceName] [nvarchar](20) UNIQUE        NOT NULL,
	[Enable]            [bit]                        NOT NULL,
	[CreateDate]        [datetime]                   NOT NULL,
	[ModifyDate]        [datetime]                       NULL,
 CONSTRAINT [PK_DonationPlace] PRIMARY KEY CLUSTERED 
(
	[DonationPlaceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DonationPlace] ADD  CONSTRAINT [DF_DonationPlace_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO

INSERT INTO [dbo].[DonationPlace] ([DonationPlaceName], [Enable]) VALUES ('Math', 1)
INSERT INTO [dbo].[DonationPlace] ([DonationPlaceName], [Enable]) VALUES ('Mission', 1)
GO




/****** Object:  Table [dbo].[DonationAccounts]    Script Date: 07/02/2011 20:33:23 ******/

CREATE TABLE [dbo].[DonationAccounts](
	[DonationAccountID]    [int]          IDENTITY(1,1) NOT NULL,
	[DonationPlaceID]      [int]                        NOT NULL,
	[DonationAccountName]  [nvarchar](20)               NOT NULL,
	[Enable]               [bit]                        NOT NULL,
	[CreateDate]           [datetime]                   NOT NULL,
	[ModifyDate]           [datetime]                       NULL,
 CONSTRAINT [PK_DonationAccounts] PRIMARY KEY CLUSTERED 
(
	[DonationAccountID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DonationAccounts]  WITH CHECK ADD CONSTRAINT [FK_DonationAccounts_DonationPlace] FOREIGN KEY([DonationPlaceID])
REFERENCES [dbo].[DonationPlace]([DonationPlaceID])
GO

ALTER TABLE [dbo].[DonationAccounts] CHECK CONSTRAINT [FK_DonationAccounts_DonationPlace]
GO

ALTER TABLE [dbo].[DonationAccounts] ADD CONSTRAINT [DF_DonationAccounts_DonationPlaceID_DonationAccountName]  UNIQUE ([DonationPlaceID], [DonationAccountName])
GO

ALTER TABLE [dbo].[DonationAccounts] ADD CONSTRAINT [DF_DonationAccounts_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO

INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (1, 'General Fund', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (1, 'Sadhu Seva', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (1, 'Temple Repairing', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (1, 'Ashram Pratistha', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (1, 'Janmashtami', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (1, 'Durgastami', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (1, 'Mother Utsav', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (1, 'Thakur Utsav', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (1, 'Swamiji Utsav', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (1, 'Mandir Pranami', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (1, 'Annual Utsav', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (2, 'General Fund', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (2, 'Dispensary', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (2, 'Fuel Charges', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (2, 'Free coaching', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (2, 'Non Formal Education', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (2, 'Primary Relief', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (2, 'Sundarban Welfare', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (2, 'Annual Utsav', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (2, 'Thakur Seva', 1)
INSERT INTO [dbo].[DonationAccounts] ([DonationPlaceID], [DonationAccountName], [Enable]) VALUES (2, 'Sadhu Seva', 1)
GO


/****** Object:  Table [dbo].[Transactions]    Script Date: 07/02/2011 20:32:41 ******/

CREATE TABLE [dbo].[Transactions](
	[TransactionID]         [int] IDENTITY(1,1) NOT NULL,
	[PersonID]              [int] NOT NULL,
	[TransactionModeID]     [int] NOT NULL,
	[TransactionModeNumber] [nvarchar](50) NULL,
	[TransactionModeDate]   [datetime] NULL,
	[TransactionAmount]     [decimal] (19,2) NOT NULL,
	[TransactionModeBank]   [nvarchar](50) NULL,
	[TransactionDate]       [datetime] NOT NULL,
	[CreateDate]            [datetime] NOT NULL,
	[ModifyDate]            [datetime] NULL,
 CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED 
(
	[TransactionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Transactions]  WITH CHECK ADD CONSTRAINT [FK_Transactions_TransactionModes] FOREIGN KEY([TransactionModeID])
REFERENCES [dbo].[TransactionModes]([TransactionModeID])
GO

ALTER TABLE [dbo].[Transactions] CHECK CONSTRAINT [FK_Transactions_TransactionModes]
GO

ALTER TABLE [dbo].[Transactions]  WITH CHECK ADD CONSTRAINT [FK_Transactions_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO

ALTER TABLE [dbo].[Transactions] CHECK CONSTRAINT [FK_Transactions_Persons]
GO

ALTER TABLE [dbo].[Transactions] WITH NOCHECK ADD CONSTRAINT [CK_TransactionAmount] CHECK  (([TransactionAmount] > 0))
GO

ALTER TABLE [dbo].[Transactions] CHECK CONSTRAINT [CK_TransactionAmount]
GO

ALTER TABLE [dbo].[Transactions] ADD  CONSTRAINT [DF_Transactions_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO




/****** Object:  Table [dbo].[TransactionReceiptDetail]    Script Date: 07/02/2011 20:31:53 ******/

CREATE TABLE [dbo].[TransactionReceiptDetail](
	[TransactionReceiptDetailID] [int] IDENTITY(1,1) NOT NULL,
	[TransactionID]              [int] UNIQUE NOT NULL,
	[DonationAccountID]          [int] NOT NULL,
	[TransactionReceiptNumber]   [nvarchar] (19) NOT NULL,
	[TransactionReceiptDate]     [datetime] NOT NULL,
	[PostalModeID]               [int] NULL,
	[DespatchDate]               [datetime] NULL,
	[CreateDate]                 [datetime] NOT NULL,
	[ModifyDate]                 [datetime] NULL,
 CONSTRAINT [PK_TransactionReceiptDetail] PRIMARY KEY CLUSTERED 
(
	[TransactionReceiptDetailID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TransactionReceiptDetail]  WITH CHECK ADD CONSTRAINT [FK_TransactionReceiptDetail_Transactions] FOREIGN KEY([TransactionID])
REFERENCES [dbo].[Transactions]([TransactionID])
GO

ALTER TABLE [dbo].[TransactionReceiptDetail] CHECK CONSTRAINT [FK_TransactionReceiptDetail_Transactions]
GO

ALTER TABLE [dbo].[TransactionReceiptDetail]  WITH CHECK ADD CONSTRAINT [FK_TransactionReceiptDetail_DonationAccounts] FOREIGN KEY([DonationAccountID])
REFERENCES [dbo].[DonationAccounts]([DonationAccountID])
GO

ALTER TABLE [dbo].[TransactionReceiptDetail] CHECK CONSTRAINT [FK_TransactionReceiptDetail_DonationAccounts]
GO

ALTER TABLE [dbo].[TransactionReceiptDetail]  WITH CHECK ADD CONSTRAINT [FK_TransactionReceiptDetail_PostalModes] FOREIGN KEY([PostalModeID])
REFERENCES [dbo].[PostalModes]([PostalModeID])
GO

ALTER TABLE [dbo].[TransactionReceiptDetail] CHECK CONSTRAINT [FK_TransactionReceiptDetail_PostalModes]
GO

ALTER TABLE [dbo].[TransactionReceiptDetail] ADD  CONSTRAINT [DF_TransactionReceiptDetail_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO
